PHP: Database gets & calls (PDO)

4. Exceptions management (the PDO::setAttribute method)

• PDO::__construct() throws a PDOException if the attempt to connect to the requested database fails.
• On the other hand, if a method fails (for example: $pdo->query) as in the previous code, the method returns false. This return does not support exceptions by default...
• To get around this problem, there are 2 solutions:
  • Use the PDO::setAttribute method to make errors returned as new PDOException instead of just returning a "false" statement:
    Code

    // PDO::setAttribute method's signature from PHP.net:
    
    public PDO::setAttribute ( int $attribute , mixed $value ) : bool
    
    // Application example:
    
    $pdo = new PDO("mysql:host=$host; dbname=$dbname", $db_username, $db_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $query = $pdo->query('SELECT * FROM posts ORDER BY date_creation DESC'); 
        // Will return an Exception instead of a fatal error in case of failure

  • While creating a new instanciation of PDO class: set a 4th parameter (the PDO class's constructor allows a array of options as a 4th parameter). Those options correspond to the PDO::setAttribute methode described just before.
    Code

    // PDO class's signature from PHP.net:
    
    public PDO::__construct ( string $dsn [, string $username [, string $passwd [, array $options ]]] )
    
    // Application exemple:
    
    try {
        // Then pass the options as the last parameter in the connection string
        $pdo = new PDO(
            "mysql:host=$host; dbname=$dbname", 
            $db_username, 
            $db_password, 
            [ // Here can be set as many attributes as needed into an associative array
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // That's how to set a failed result as a nex Exception
                PDO::ATTR_CASE => PDO::CASE_NATURAL,
                PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING
            ]
        $query = $pdo->query('SELECT * FROM posts ORDER BY date_creation DESC'); // Will return an Exception instead of a fatal error in case of failure
        );
    } catch(PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }

$pdo = new PDO("sqlite:$dsn", null, null, [ 
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
]);
try {
    $pdo->beginTransaction();
        $pdo->exec('UPDATE posts SET name = "Test title" WHERE id = 3');
        $pdo->exec('UPDATE posts SET content = "Test content" WHERE id = 3');
        $post = $pdo->query('SELECT * FROM posts WHERE id = 3')->fetch();
    $pdo->commit();
    echo '<b>Updated post data:</b>';
    var_dump($post);
} catch (Exception $e) {
    $pdo->rollback();
    echo '<b>No change has been made to database due to a processing error:</b><br>' .
        $e->getMessage();
}

object(stdClass)#1 (4) {
  ["id"]=>
  string(1) "3"
  ["title"]=>
  string(10) "Test title"
  ["content"]=>
  string(12) "Test content"
  ["date_creation"]=>
  string(10) "1606847434"
}